IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncSchedJobStepMSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncSchedJobStepMSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncSchedJobStepMSSQL
     @SrvrInstncId                   int,
     @PackageExecutionId             bigint,
     @SrvrInstncSchedJobId           int,
     @StepIdentifier                 uniqueidentifier,
     @StepId                         int,
     @StepName                       varchar(128),
     @SubSystem                      varchar(40),
     @Command                        nvarchar(MAX),
     @IncludeStepOutputInHistoryFlag tinyint,
     @LogToTableFlag                 tinyint,
     @LogToTableAppendOutputFlag     tinyint,
     @AppendOutputToOutputFileFlag   tinyint,
     @CmdExecSuccessCode             int,
     @OnSuccessAction                varchar(30),
     @OnFailAction                   varchar(30),
     @OnSuccessStepId                int,
     @OnFailStepId                   int,
     @ServerName                     varchar(128),
     @DatabaseName                   varchar(128),
     @DatabaseUserName               varchar(128),
     @RetryAttempts                  int,
     @RetryIntervalInMinutes         int,
     @OSRunPriority                  int,
     @OutputFileName                 varchar(200),
     @ProxyName                      varchar(128),
     @StepDeletedFlag                tinyint       = 0
AS
/*
 *
 * SaveSrvrInstncSchedJobStepMSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/10/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @StartDt     datetime,
             @PEI         bigint,
             @RC          int,
             @NOW         datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Condition Flags
     SELECT @StepDeletedFlag = CASE WHEN @StepDeletedFlag != 1 OR @StepDeletedFlag IS NULL THEN 0 ELSE 1 END

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncSchedJobStepMSSQL
                     WHERE  SrvrInstncId         = @SrvrInstncId 
                     AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
                     AND    StepId               = @StepId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncSchedJobStepMSSQL
               ( SrvrInstncId,
                 SrvrInstncSchedJobId,
                 StepId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 PackageExecutionId,
                 StepDeletedFlag,
                 StepIdentifier,
                 StepName,
                 SubSystem,
                 Command,
                 IncludeStepOutputInHistoryFlag,
                 LogToTableFlag,
                 LogToTableAppendOutputFlag,
                 AppendOutputToOutputFileFlag,
                 CmdExecSuccessCode,
                 OnSuccessAction,
                 OnFailAction,
                 OnSuccessStepId,
                 OnFailStepId,
                 ServerName,
                 DatabaseName,
                 DatabaseUserName,
                 RetryAttempts,
                 RetryIntervalInMinutes,
                 OSRunPriority,
                 OutputFileName,
                 ProxyName )
          SELECT @SrvrInstncId,
                 @SrvrInstncSchedJobId,
                 @StepId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @PackageExecutionId,
                 0, -- StepDeletedFlag
                 @StepIdentifier,
                 @StepName,
                 @SubSystem,
                 @Command,
                 @IncludeStepOutputInHistoryFlag,
                 @LogToTableFlag,
                 @LogToTableAppendOutputFlag,
                 @AppendOutputToOutputFileFlag,
                 @CmdExecSuccessCode,
                 @OnSuccessAction,
                 @OnFailAction,
                 @OnSuccessStepId,
                 @OnFailStepId,
                 @ServerName,
                 @DatabaseName,
                 @DatabaseUserName,
                 @RetryAttempts,
                 @RetryIntervalInMinutes,
                 @OSRunPriority,
                 @OutputFileName,
                 @ProxyName
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncSchedJobStepMSSQL
          WHERE  SrvrInstncId         = @SrvrInstncId
          AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
          AND    StepId               = @StepId
          AND    CurrentRecordFlag    = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncSchedJobStepMSSQL  
          SET    EndDt             = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag = 0
          WHERE  SrvrInstncId         = @SrvrInstncId 
          AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
          AND    StepId               = @StepId
          AND    CurrentRecordFlag    = 1

          IF ( @StepDeletedFlag = 0 )
          BEGIN -- Do regular insert
               INSERT dbo.SrvrInstncSchedJobStepMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      StepId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      StepDeletedFlag,
                      StepIdentifier,
                      StepName,
                      SubSystem,
                      Command,
                      IncludeStepOutputInHistoryFlag,
                      LogToTableFlag,
                      LogToTableAppendOutputFlag,
                      AppendOutputToOutputFileFlag,
                      CmdExecSuccessCode,
                      OnSuccessAction,
                      OnFailAction,
                      OnSuccessStepId,
                      OnFailStepId,
                      ServerName,
                      DatabaseName,
                      DatabaseUserName,
                      RetryAttempts,
                      RetryIntervalInMinutes,
                      OSRunPriority,
                      OutputFileName,
                      ProxyName )
               SELECT @SrvrInstncId,
                      @SrvrInstncSchedJobId,
                      @StepId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      0, -- StepDeletedFlag
                      @StepIdentifier,
                      @StepName,
                      @SubSystem,
                      @Command,
                      @IncludeStepOutputInHistoryFlag,
                      @LogToTableFlag,
                      @LogToTableAppendOutputFlag,
                      @AppendOutputToOutputFileFlag,
                      @CmdExecSuccessCode,
                      @OnSuccessAction,
                      @OnFailAction,
                      @OnSuccessStepId,
                      @OnFailStepId,
                      @ServerName,
                      @DatabaseName,
                      @DatabaseUserName,
                      @RetryAttempts,
                      @RetryIntervalInMinutes,
                      @OSRunPriority,
                      @OutputFileName,
                      @ProxyName
               FROM   dbo.SrvrInstncSchedJobStepMSSQL
               WHERE  SrvrInstncId         = @SrvrInstncId 
               AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
               AND    StepId               = @StepId
               AND    StartDt              = @StartDt
               -- Decide if there will be an insert
               AND  ( StepDeletedFlag = 1
                      --1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR ( @StepIdentifier                 != StepIdentifier )
                   OR ( @StepName                       != StepName )
                   OR ( @SubSystem                      != SubSystem )
                   OR ( @Command                        != Command )
                   OR ( @IncludeStepOutputInHistoryFlag != IncludeStepOutputInHistoryFlag )
                   OR ( @LogToTableFlag                 != LogToTableFlag )
                   OR ( @LogToTableAppendOutputFlag     != LogToTableAppendOutputFlag )
                   OR ( @AppendOutputToOutputFileFlag   != AppendOutputToOutputFileFlag )
                   OR ( @CmdExecSuccessCode             != CmdExecSuccessCode )
                   OR ( @OnSuccessAction                != OnSuccessAction )
                   OR ( @OnFailAction                   != OnFailAction )
                   OR ( @OnSuccessStepId                != OnSuccessStepId )
                   OR ( @OnFailStepId                   != OnFailStepId )
                   OR ( @RetryAttempts                  != RetryAttempts )
                   OR ( @RetryIntervalInMinutes         != RetryIntervalInMinutes )
                   OR ( @OSRunPriority                  != OSRunPriority )
                   -- Handle nullable columns
                   --OR ( @StepIdentifier   != StepIdentifier 
                   --  OR ( @StepIdentifier   IS     NULL AND StepIdentifier   IS NOT NULL )
                   --  OR ( @StepIdentifier   IS NOT NULL AND StepIdentifier   IS     NULL ) )
                   OR ( @ServerName       != ServerName 
                     OR ( @ServerName       IS     NULL AND ServerName       IS NOT NULL ) 
                     OR ( @ServerName       IS NOT NULL AND ServerName       IS     NULL ) )
                   OR ( @DatabaseName     != DatabaseName
                     OR ( @DatabaseName     IS     NULL AND DatabaseName     IS NOT NULL )
                     OR ( @DatabaseName     IS NOT NULL AND DatabaseName     IS     NULL ) )
                   OR ( @DatabaseUserName != DatabaseUserName
                     OR ( @DatabaseUserName IS     NULL AND DatabaseUserName IS NOT NULL )
                     OR ( @DatabaseUserName IS NOT NULL AND DatabaseUserName IS     NULL ) )
                   OR ( @OutputFileName   != OutputFileName
                     OR ( @OutputFileName   IS     NULL AND OutputFileName   IS NOT NULL )
                     OR ( @OutputFileName   IS NOT NULL AND OutputFileName   IS     NULL ) )
                   OR ( @ProxyName        != ProxyName
                     OR ( @ProxyName        IS     NULL AND ProxyName        IS NOT NULL )
                     OR ( @ProxyName        IS NOT NULL AND ProxyName        IS     NULL ) )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               INSERT dbo.SrvrInstncSchedJobStepMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      StepId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      StepDeletedFlag,
                      StepIdentifier,
                      StepName,
                      SubSystem,
                      Command,
                      IncludeStepOutputInHistoryFlag,
                      LogToTableFlag,
                      LogToTableAppendOutputFlag,
                      AppendOutputToOutputFileFlag,
                      CmdExecSuccessCode,
                      OnSuccessAction,
                      OnFailAction,
                      OnSuccessStepId,
                      OnFailStepId,
                      ServerName,
                      DatabaseName,
                      DatabaseUserName,
                      RetryAttempts,
                      RetryIntervalInMinutes,
                      OSRunPriority,
                      OutputFileName,
                      ProxyName )
               SELECT SrvrInstncId,
                      SrvrInstncSchedJobId,
                      StepId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      1, -- StepDeletedFlag
                      StepIdentifier,
                      StepName,
                      SubSystem,
                      Command,
                      IncludeStepOutputInHistoryFlag,
                      LogToTableFlag,
                      LogToTableAppendOutputFlag,
                      AppendOutputToOutputFileFlag,
                      CmdExecSuccessCode,
                      OnSuccessAction,
                      OnFailAction,
                      OnSuccessStepId,
                      OnFailStepId,
                      ServerName,
                      DatabaseName,
                      DatabaseUserName,
                      RetryAttempts,
                      RetryIntervalInMinutes,
                      OSRunPriority,
                      OutputFileName,
                      ProxyName
               FROM   dbo.SrvrInstncSchedJobStepMSSQL 
               WHERE  SrvrInstncId         = @SrvrInstncId
               AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
               AND    StepId               = @StepId
               AND    StartDt              = @StartDt

               SELECT @RC = @@ROWCOUNT 
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance scheduled job step MS SQL specific record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncSchedJobStepMSSQL;
GO
